
from flask import Flask, render_template
from flask import request, redirect,url_for
app=Flask(__name__)
from flask import Flask,request,render_template


#数据库导入
import sqlite3
conn = sqlite3.connect('database.db')
print("Opened database successfully")
cursor=conn.cursor()
#检查是否存在已有数据库，有则存在，无则创建
def table_creation(cursor,table_name):
    cursor.execute(f"select name from sqlite_master where type='table' and name='student';")
    return cursor.fetchone() is not None
if not table_creation(cursor,'student'):
    cursor.execute('CREATE TABLE student (name TEXT, addr TEXT, city TEXT, pin TEXT,password TEXT)')
    print("Table created successfully")
else:
    print("table already exists")
conn.close()

#数据库生产表，并从用户的输入获得数据并输出
@app.route('/addrec', methods=['POST', 'GET'])
def addrec():
    if request.method=='POST' and request:
        addrec=request.form
        nm=(addrec['name'])
        addr=(addrec['addr'])
        city=(addrec['city'])
        pin=(addrec['pin'])
        ps=(addrec['password'])
        with sqlite3.connect("database.db") as con:
            cursor=con.cursor()
            cursor.execute("insert into student (name,addr,city,pin,password)values (?,?,?,?,?)",(nm,addr,city,pin,ps))
            con.commit()
            msg="success!"
            return render_template('result.html',msg=msg)
            con.close()

    else:
        print("somthing wrong happening")
        return  render_template('result.html')


@app.route("/enter")
def enter():
    return render_template('index.html')

#产生表后输出可视化界面
@app.route('/list')
def list():
    con = sqlite3.connect("database.db")
    con.row_factory = sqlite3.Row

    cur = con.cursor()
    cur.execute("select * from student")

    rows = cur.fetchall()
    return render_template("list.html", rows=rows)

@app.route('/')
def login():
    return render_template('login.html')

#主界面重定义
@app.route('/login')
def backup():
    return redirect(url_for("login"))

if __name__=="__main__":
    app.run(debug=True)